# %%
import pandas as pd


#%%
def get_rank_56():
    #%%
    name_book = pd.read_excel('data/56计算机/21计算机5.6班名单-加入学号.xlsx', sheet_name='Sheet1')
    origin_scores = pd.read_excel('data/56计算机/tzcoder竞赛20211228.xlsx')
    base_scores = name_book.merge(origin_scores, on=('姓名',), how='left')[['用户', '学号_x', '姓名', '已解决', '难度']] \
        .rename(columns={'已解决': '2021-12-28已解决', '难度': '2021-12-28难度'})

    #%%
    dfs = pd.read_html('http://tzcoder.cn/acmhome/classes.do?method=allClassUsers&classId=364', encoding='GBK',
                       match='游戏币')

    #%%
    current_scores = name_book.merge(dfs[1], on='姓名', how='left')[['用户', '姓名', '已解决', '难度']] \
        .rename(columns={'已解决': '当前已解决', '难度': '当前难度'})
    merged_scores = base_scores.merge(current_scores, on='姓名')
    merged_scores['已解决增量'] = merged_scores['当前已解决'] - merged_scores['2021-12-28已解决']
    merged_scores['难度增量'] = merged_scores['当前难度'] - merged_scores['2021-12-28难度']
    merged_scores['评分'] = merged_scores['已解决增量'] * 0.5 + merged_scores['难度增量'] * 0.5
    sorted_scores = merged_scores.sort_values(by='评分', ascending=False)\
        .reset_index(drop=True).drop('用户_y', axis=1).drop('用户_x', axis=1).drop('学号_x', axis=1)

    html = sorted_scores.to_html(classes='scores')
    #%%
    return html


def get_rank_qax():
    #%%
    name_book = pd.read_excel('data/齐安信/21计算机“奇安信班”名单.xlsx', sheet_name='报名汇总表')
    origin_scores_12 = pd.read_excel('data/齐安信/21计算机20220117ACM数据.xlsx', sheet_name='12')
    origin_scores_34 = pd.read_excel('data/齐安信/21计算机20220117ACM数据.xlsx', sheet_name='34')
    origin_scores_56 = pd.read_excel('data/齐安信/21计算机20220117ACM数据.xlsx', sheet_name='56')
    origin_scores = pd.concat([origin_scores_12, origin_scores_34, origin_scores_56]).reset_index(drop=True)
    #%%
    base_scores = name_book.merge(origin_scores, how='left', on='姓名')[['学号_x', '姓名', '已解决', '难度']]\
        .rename(columns={'学号_x': '学号'}) \
        .rename(columns={'已解决': '2022-01-17已解决', '难度': '2022-01-17难度'})
    #%%
    dfs12 = pd.read_html('http://tzcoder.cn/acmhome/classes.do?method=allClassUsers&classId=281', encoding='GBK',
                       match='游戏币')
    dfs34 = pd.read_html('http://tzcoder.cn/acmhome/classes.do?method=allClassUsers&classId=337', encoding='GBK',
                       match='游戏币')
    dfs56 = pd.read_html('http://tzcoder.cn/acmhome/classes.do?method=allClassUsers&classId=364', encoding='GBK',
                       match='游戏币')
    current_scores = pd.concat([dfs12[1], dfs34[1], dfs56[1]]).reset_index(drop=True)
    current_scores = name_book.merge(current_scores, how='left', on='姓名')[['学号_x', '姓名', '已解决', '难度']]\
        .rename(columns={'学号_x': '学号'}).rename(columns={'已解决': '当前已解决', '难度': '当前难度'})
    #%%
    merged_scores = base_scores.merge(current_scores, on='姓名')
    merged_scores['已解决增量'] = merged_scores['当前已解决'] - merged_scores['2022-01-17已解决']
    merged_scores['难度增量'] = merged_scores['当前难度'] - merged_scores['2022-01-17难度']
    merged_scores['评分'] = merged_scores['已解决增量'] * 0.5 + merged_scores['难度增量'] * 0.5
    sorted_scores = merged_scores.sort_values(by='评分', ascending=False).reset_index(drop=True)\
        .drop('学号_y', axis=1).drop('学号_x', axis=1)

    html = sorted_scores.to_html(classes='scores')
    # %%
    return html
